PostgreSQLの新機能BRIN INDEXを使ってみた。

はじめに

BRIN INDEXを使うとビッグデータの検索が高速になるとの事なので気になって調べてみました。

試してみる

テーブル定義

CREATE TABLE table_brin (
id serial,
title VARCHAR(20),
date_normal DATE,
date_index DATE,
PRIMARY KEY(id),
CONSTRAINT date_brinindex_key UNIQUE(date_index));

# \d table_brin
                                   Table "public.table_brin"
   Column    |         Type          |                        Modifiers                        
-------------+-----------------------+---------------------------------------------------------
 id          | integer               | not null default nextval('table_brin_id_seq'::regclass)
 title       | character varying(20) | 
 date_normal | date                  | 
 date_index  | date                  | 
Indexes:
    "table_brin_pkey" PRIMARY KEY, btree (id)

ここに1000万件のデータを持つCSVをcopyで入れます。
CSVの容量は471.9MBでした。

こんなテーブルになります。
日付を表すdate_normalとdate_indexの値が１日づつ増えるテーブルです。

    id    |     title      | date_normal | date_index  
----------+----------------+-------------+-------------
        1 |  title1        | 2016-03-02  | 2016-03-02
        2 |  title2        | 2016-03-03  | 2016-03-03
        3 |  title3        | 2016-03-04  | 2016-03-04
        4 |  title4        | 2016-03-05  | 2016-03-05
        5 |  title5        | 2016-03-06  | 2016-03-06
.
.
.
  9999998 |  title9999998  | 29395-03-25 | 29395-03-25
  9999999 |  title9999999  | 29395-03-26 | 29395-03-26
 10000000 |  title10000000 | 29395-03-27 | 29395-03-27

次はBRIN INDEXを適用してから計測してみます。

BRIN INDEX構文

CREATE INDEX インデックス名 ON テーブル名 USING brin(カラム名);

BRIN INDEX実行

date_indexをBRIN INDEXに適用させます。

CREATE INDEX index_brin ON table_brin USING brin(date_index);

3528.346msかかりました。
テーブル定義を確認してみます。

# \d table_brin
                                   Table "public.table_brin"
   Column    |         Type          |                        Modifiers                        
-------------+-----------------------+---------------------------------------------------------
 id          | integer               | not null default nextval('table_brin_id_seq'::regclass)
 title       | character varying(20) | 
 date_normal | date                  | 
 date_index  | date                  | 
Indexes:
    "table_brin_pkey" PRIMARY KEY, btree (id)
    "index_brin" brin (date_index)

Indexesにindex_brinが追加されていますね。

SELECT実行例１

最初に\timingで処理時間を表示させる設定にします。
それからBRIN INDEXの対象では無いdate_normalで検索してみます。

# \timing
Timing is on.

SELECT COUNT(*) FROM table_brin WHERE date_normal BETWEEN '2016-04-01' AND '2100-01-01';

  count 
-------
 25789
(1 row)

Time: 2097.534 ms

2097.534msでした。

次は、BRIN INDEXの対象で有るdate_indexで検索してみます。

SELECT COUNT(*) FROM table_brin WHERE date_index BETWEEN '2016-04-01' AND '2100-01-01';

 count 
-------
 25789
(1 row)

Time: 23.848 ms

23.848msとなり、数値上も体感上も違いが出ました。

SELECT実行例２

今度は検索範囲を広げてみます。
まずはdate_normalで検索。

# SELECT COUNT(*) FROM table_brin WHERE date_normal BETWEEN '2016-04-01' AND '25000-01-01';

  count  
---------
 7078989
(1 row)

Time: 2738.185 ms

実行例１より少し増えました。

次にdate_indexで検索。

# SELECT COUNT(*) FROM table_brin WHERE date_index BETWEEN '2016-04-01' AND '25000-01-01';

  count  
---------
 7078989
(1 row)

Time: 2997.346 ms

date_normalを越えてしまいました。
1000万件でこれなので1億件だったら...

さいごに

使い分けが必要だと感じました。
他の条件で試してみたらまた違う結果になるかもしれないので、みなさんも是非試してみてください。